package com.koolearn.test;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Maps;
import com.koolearn.lms.core.DataTransform;
import com.koolearn.lms.core.ExcelGenerator;
import com.koolearn.lms.util.HttpUtil;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;

/**
 * Created by hanchao on 2016/9/6.
 */
public class Study99Export {
    private static String sql = "SELECT s.ORDER_NO AS 订单号,s.order_id AS 订单ID, s.USER_ID  AS 用户ID, to_char(o.ORDERTIME, 'yyyy-MM-dd hh24:mi:ss') AS 订单时间,   to_char(o.PAYTIME, 'yyyy-MM-dd hh24:mi:ss')   AS 到款时间,   (select wm_concat(p.name) from product_version p where oi.PROduct_ID =p.PRODUCT_ID and oi.VERSION_ID = p.VERSION_ID) as 产品名称,   (select case when s.PAY_WAY=57 then '余额支付' else concat('余额支付+',pw.name) end  from PAYWAY pw where s.PAY_WAY = pw.ID) as 付款方式,   ad.ALLIANCE_ID as 联盟ID,   ad.KID as 关键字ID,   'tmp' as 关键字,round(o.AMOUNT*o.VALID_INCOME_PERCENT/100,2) as 实际收入 FROM ACTIVITY_CHARGE_STATISTICS s, orders o,ORDERITEM oi ,AD_ALLIANCEORDER ad  where s.ORDER_ID=o.id and oi.ORDER_ID=o.ID and s.ORDER_ID=ad.ORDER_ID  and s.CREATE_TIME>to_date('2016-09-02 00:00:00','yyyy-mm-dd hh24:mi:ss') and s.CREATE_TIME <to_date('2016-09-06 23:59:59','yyyy-mm-dd hh24:mi:ss') ";

    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        ExcelGenerator generator = context.getBean(ExcelGenerator.class);
        final ObjectMapper mapper = context.getBean(ObjectMapper.class);
        generator.generate(sql,new File("学习节网盟订单.xls"),new DataTransform(){
            @Override
            public List<Map<String, Object>> transform(List<Map<String, Object>> datas) {
                Map<String,String> kidMapping = Maps.newHashMap();
                for(Map<String,Object> map : datas){
                    String key = String.valueOf(map.get("关键字ID"));
                    if(map.get("关键字ID") != null){
                        if(kidMapping.containsKey(key)){
                            System.out.println("contains:"+key);
                            map.put("关键字",String.valueOf(kidMapping.get(key)));
                            continue;
                        }
                        Map<String,String> headers = Maps.newHashMap();
                        headers.put("Referer","koolearn.com");
                        Map<String,String> params = Maps.newHashMap();
                        params.put("keywords", key);
                        String kidWords = HttpUtil.executeGet("http://un.koolearn.com/proxy/keywordSearchJson.do",params,headers);
                        System.out.println("no contains:"+key);
                        try {
                            List<Map> temp = mapper.readValue(kidWords,new TypeReference<List<Map>>(){});
                            for(Map m : temp){
                                map.put("关键字",String.valueOf(m.get("NAME")));
                                kidMapping.put(String.valueOf(m.get("ID")),String.valueOf(m.get("NAME")));
                            }
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }else{
                        map.put("关键字ID","  ");
                        map.put("关键字","  ");
                    }
                }

                return datas;
            }
        });
    }
}
